package com.ywjh.inter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.ywjh.inter.entity.UsersEntity;

public class UsersDAO {
	public int insert(Connection conn,UsersEntity e) throws SQLException {
		int id = -1;
		PreparedStatement ps=conn.prepareStatement("INSERT INTO users ( loginname, password, phone, register_time, last_login_time, is_doc, status) VALUES (?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, e.getLoginname());
		ps.setString(2, e.getPassword());
		ps.setString(3, e.getPhone());
		ps.setTimestamp(4, e.getRegister_time());
		ps.setTimestamp(5, e.getLast_login_time());
		ps.setInt(6, e.getIs_doc());
		ps.setInt(7, e.getStatus());
		if (ps.executeUpdate() > 0) {
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				id = rs.getInt(1);
			}
		}
		return id;
	}
	
	public boolean update(Connection conn,UsersEntity e) throws SQLException {
		boolean b=false;
		PreparedStatement ps=conn.prepareStatement("update users set loginname=?, password=?, phone=?, register_time=?, last_login_time=?, is_doc=?, status=?,head_url=? where id =?");
		ps.setString(1, e.getLoginname());
		ps.setString(2, e.getPassword());
		ps.setString(3, e.getPhone());
		ps.setTimestamp(4, e.getRegister_time());
		ps.setTimestamp(5, e.getLast_login_time());
		ps.setInt(6, e.getIs_doc());
		ps.setInt(7, e.getStatus());
		ps.setString(8, e.getHead_url());
		ps.setInt(9, e.getId());
		if (ps.executeUpdate() > 0) {
			b=true;
		}
		return b;
	}
	
	public UsersEntity findByPhone(Connection conn,String phone) throws SQLException {
		UsersEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from users where phone=? and status=1");
		ps.setString(1, phone);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}
	
	private UsersEntity fill(ResultSet rs) throws SQLException {
		UsersEntity e=new UsersEntity();
		e.setId(rs.getInt("id"));
		e.setIs_doc(rs.getInt("is_doc"));
		e.setLast_login_time(rs.getTimestamp("last_login_time"));
		e.setLoginname(rs.getString("loginname"));
		e.setPassword(rs.getString("password"));
		e.setPhone(rs.getString("phone"));
		e.setRegister_time(rs.getTimestamp("register_time"));
		e.setStatus(rs.getInt("status"));
		e.setHead_url(rs.getString("head_url"));
		return e;
	}

	public UsersEntity findById(Connection conn,int id) throws SQLException {
		UsersEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from users where id=? and status=1");
		ps.setInt(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}

	public UsersEntity findByAccount(Connection conn, String loginname, String password) throws SQLException {
		UsersEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from users where loginname=? and password=? and status=1");
		ps.setString(1, loginname);
		ps.setString(2, password);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}

	public List<UsersEntity> searchByKeyword(Connection conn, String keyword, String array) throws SQLException {
		System.out.println("friend is="+array);
		
		 List<UsersEntity> list=new LinkedList<UsersEntity>();
		 if(array==null||"".equals(array)) {
				return list;
			}
		 PreparedStatement ps=conn.prepareStatement("select a.* from users a,profile b where (a.loginname like ? or a.phone like ? or b.name like ? or b.memo like ?) and a.status=1 and a.id=b.user_id and a.id in ("+array+")");
		 ps.setString(1, "%"+keyword+"%");
		 ps.setString(2, "%"+keyword+"%");
		 ps.setString(3, "%"+keyword+"%");
		 ps.setString(4, "%"+keyword+"%");
		 ResultSet rs=ps.executeQuery();
		 while(rs.next()) {
			 UsersEntity u=fill(rs);
			 list.add(u);
		 }
		 rs.close();
		 ps.close();
		 ps=conn.prepareStatement("select a.* from users a,doc_profile b where (a.loginname like ? or a.phone like ? or b.name like ? or b.memo like ?) and a.status=1 and a.id=b.user_id and a.id in ("+array+")");
		 ps.setString(1, "%"+keyword+"%");
		 ps.setString(2, "%"+keyword+"%");
		 ps.setString(3, "%"+keyword+"%");
		 ps.setString(4, "%"+keyword+"%");
		 rs=ps.executeQuery();
		 while(rs.next()) {
			 UsersEntity u=fill(rs);
			 list.add(u);
		 }
		return list;
	}
	
	public List<UsersEntity> searchByKeyword(Connection conn, String keyword) throws SQLException {
		 List<UsersEntity> list=new LinkedList<UsersEntity>();
		 PreparedStatement ps=conn.prepareStatement("select a.* from users a,profile b where (a.loginname like ? or a.phone like ? or b.name like ? or b.memo like ?) and a.status=1 and a.id=b.user_id");
		 ps.setString(1, "%"+keyword+"%");
		 ps.setString(2, "%"+keyword+"%");
		 ps.setString(3, "%"+keyword+"%");
		 ps.setString(4, "%"+keyword+"%");
		 ResultSet rs=ps.executeQuery();
		 while(rs.next()) {
			 UsersEntity u=fill(rs);
			 list.add(u);
		 }
		 rs.close();
		 ps.close();
		 ps=conn.prepareStatement("select a.* from users a,doc_profile b where (a.loginname like ? or a.phone like ? or b.name like ? or b.memo like ?) and a.status=1 and a.id=b.user_id");
		 ps.setString(1, "%"+keyword+"%");
		 ps.setString(2, "%"+keyword+"%");
		 ps.setString(3, "%"+keyword+"%");
		 ps.setString(4, "%"+keyword+"%");
		 rs=ps.executeQuery();
		 while(rs.next()) {
			 UsersEntity u=fill(rs);
			 list.add(u);
		 }
		return list;
	}

	public UsersEntity findByLoginname(Connection conn, String loginname, String pwd) throws SQLException {
		UsersEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from users where (loginname=? or phone=?) and password=? and status=1");
		ps.setString(1, loginname);
		ps.setString(2, loginname);
		ps.setString(3, pwd);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}

	public UsersEntity findByLoginname(Connection conn, String loginname) throws SQLException {
		UsersEntity e=null;
		PreparedStatement ps=conn.prepareStatement("select * from users where (loginname=?) and status=1");
		ps.setString(1, loginname);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			e=fill(rs);
		}
		return e;
	}
	
	
	
}
